from db_interface import dbApi
import hashlib # password checking
import json
import datetime
import time
from mail_api import *
'''
Support application logical model
'''



class baseModel(object):
    '''
    Base model class contains base prototypes database
    abstractions for interacting with database objects. 
    Derived classes can override these methods to create
    custom data providers.
    
    '''

    def __init__(self, dbEngineType='sqlite3'):
        '''
        Setup initial database connection for
        all database interactions
        '''
        #Note: Initial release will only support SQLITE3 embedded database
        # other databases could be added later. Hardcoding method connection here... 
        dbType = dbApi(dbEngineType)
        self.db = dbType.open()
        
    ''' 
    --- START BASE CLASS DEFINITIONS ---1
    Create abstract base methods
    These methods will be available to all derived method. 
    Used as base calls for data operations.
    '''    
    
    
    def getMetaData(self, queryResult):
        '''
        Extract meta data from query
        '''
        metaDataList = [metaData[0] for metaData in queryResult.description]
        return metaDataList
    
    def getDataDict(self, queryResult, getOne=None):
        '''
        Returns result set as a data dictionary
        '''
        columnNames = self.getMetaData(queryResult)
        if getOne:
            '''
            Return a single row
            '''
            return dict(zip(columnNames, queryResult.fetchone())) 
        else:
            '''
            Return all rows
            '''
            return [dict(zip(columnNames, dataElement)) for dataElement in queryResult.fetchall()]
        
        
        
    def getJsonResults(self, queryResult):
        '''
        package data as json
        '''
        return json.dumps(self.getDataDict(queryResult))
    
    def getJsonData(self, data):
        '''
        Convert structured python data
        into json format. 
        '''
        return json.dumps(data)
    
    def getNowTs(self):
        '''
        return a timestamp for now
        '''
        return int(time.time())

    def getDateFromTs(self, ts):
        '''
        Given a timestamp return data string
        
        '''
        tc = datetime.datetime.fromtimestamp(ts)
        
        return "%s/%s/%s"  %(tc.month, tc.day, tc.year)

    def getDatePartsFromTs(self, ts):
        
        tc = datetime.datetime.fromtimestamp(ts)
        return tc.timetuple()
    
    
    def getDurFromTs(self, Sts, Ets):
        '''
        Get duration from 2 time stamps
        '''
        
        dur = {}
        tsDur = Ets - Sts
        dur['HOUR'] = tsDur / 3600
        dur['MIN'] = (tsDur % 3600) / 60
        
        return dur


class userControl(baseModel):
    '''
    Provide user level application methods
    Services:
    1.) Basic user CRUD (CReate, Update, and Delete )
    2.) Login Handler
    3.) User ID Control 
    '''
    
    """ Note: Init called by base class...
    def __init__(self):
        '''
        Setup database interface
        TODO: MOVE TO BASE CLASS LATER!!!!

        '''
        dbType = dbApi('sqlite3')
        self.db = dbType.open()
        
    """    
        
    def checkLogin(self, username, passwd):
        '''
        Get user information from data layer
        Perform checks
        Return results
        '''
        
        # Convert given passwd to hash
        md5pass = hashlib.md5(passwd)
        userData = self.getUserByUname(username)
        print "got this result -> %s" %( userData )
        
        try:
            '''
            Return uid if user information matches
            username && password in database
            Otherwise return None...  
            '''
            print "Testing login Credentials... (%s, %s [%s]) - (%s, %s)" %  (username, passwd, md5pass.hexdigest(), userData['uname'], userData['password'])
            
            if username == userData['uname']: 
                print "[checkLogin] User name is valid"
                if md5pass.hexdigest() == userData['password']:
                    print "[checkLogin] password is valid "
                    '''
                       Return user id to seed in the session cache
                    '''
                    userReturnData = {}
                    userReturnData['ID'] = userData['uid']
                    userReturnData['First'] = userData['namefirst']
                    userReturnData['Last'] = userData['namelast']
                    userReturnData['Email'] = userData['email']
                    userReturnData['Admin'] = userData['isAdmin']
                    
                    return userReturnData
                else:
                    print "Password Failed"
            else:
                print "Login Failed"
                   
        except:
            '''
            Catch all exceptions
            '''
            print "Error processing data!!!!!!!!!!! "
            return None
            
        
    
    def getWsAllUsers(self, uid=None):
        '''
        Return all user information
        
        '''
        sql = "select * from users" 
        result = self.db.query(sql)
        return self.getJsonResults(result)
    
    def getWsEventUsers(self, uid):
        '''
        When scheduling a event do not include
        the host in the event listing.
        '''
        sql = "select * from users where uid != %s" %(uid) 
        result = self.db.query(sql)
        return self.getJsonResults(result)

    def getUserByUname(self, uname=None):
        '''
        Return single row containing user information
        If data is not found return -> None...
        '''
        sql = "select * from users where uname ='%s'" %(uname)
        result = self.db.query(sql)
        return result.fetchone()
        
    def getUserById(self, uid , fullName=None , format='python'):
        '''
        Return single row containing user information
        If data is not found return -> None...
        '''
        sql = "select * from users where uid ='%s'" %(uid)
        #print "User ById: %s"%(sql)
        result = self.db.query(sql)
        if fullName:
            '''
            Format name
            '''
            nameInfo = result.fetchone()
            name = "%s, %s" %(nameInfo['namelast'], nameInfo['namefirst'])
            #print "Returning: %s " %(name)
            return name
        if format == 'json':
            '''
            Return JSON
            '''
            data = self.getDataDict(result, 1)
            return self.getJsonData(data)
        
        else:
            '''
            Return python
            '''
            return result.fetchone()
        

    def getWsUserById(self, uid , fullName=None, format='python' ):
        '''
        Process multiple requests 
        Requires array as input...
        Return single row containing user information
        If data is not found return -> None...
        '''
        sql = "select * from users where uid ='%s'" %(uid)
        result = self.db.query(sql)
        if fullName:
            '''
            Format name
            '''
            name = "%s, %s" %(result['namelast'], result['namefirst'])
            return name
        
        if format == 'json':
            '''
            Return JSON
            '''
            return self.getJsonData(result.fetchone())
        
        else:
            '''
            Return python
            '''
            return result.fetchone()

    
    
    def getAllUsers(self):
        '''
        Return all user information
        
        '''
        sql = "select * from users" 
        result = self.db.query(sql)
        return result.fetchall()

class eventControl(baseModel):
    '''
    Handle requests for event data...
    '''
    
    def getEventById(self, mid):
        '''
        Return Event data by ID
        Return a complete set of data regarding the event
        this data can be used to edit the form as well
        '''
        data = {}
        sql = """select 
        e.mid as eventID,
        e.mhostid as hostid,
        u.namelast || ", " || u.namefirst as hostName, 
        e.mstarttime as start,
        datetime(e.mstarttime, 'unixepoch', 'localtime') as startDt, 
        e.mendtime as end, 
        datetime(e.mendtime, 'unixepoch', 'localtime') as endDt,
        e.mdescription as title,
        e.mstatusid as eventStatusID,
        s.msdescription as eventStatusDesc,
        r.rname as room,
        r.rdescription as rDesc,
        r.rsize as roomSize
        from 
        events e 
        LEFT JOIN
        rooms r
        ON
        r.rid = e.mroomid
        LEFT JOIN
        mstatus s
        ON
        e.mstatusid = s.msid
        LEFT JOIN
        users u
        ON
        e.mhostid = u.uid
        where 
        e.mid = %s;
        """   %(mid)
        #print "[EVENT - 1 ] sql -> %s" %(sql)
        result = self.db.query(sql)
        data['Event'] = self.getDataDict(result, 1)
        sql = """select 
        a.uid as userID,
        a.asid as statusID,
        s.asdescription as statusDesc,
        u.namelast || ", "|| u.namefirst as listName
        from 
        attendees a
        LEFT JOIN 
        users u
        ON
        a.uid = u.uid
        LEFT JOIN
        astatus s
        ON 
        a.asid = s.asid
        WHERE 
        a.mid = %s
        order by u.namelast asc;
        """ %(mid)
        #print "[EVENT - 1 ] sql -> %s" %(sql)
        result = self.db.query(sql)
        data['Attendees'] =  self.getDataDict(result)
        #print "Event Data -> %s" %(data)
        return data

class adminControl(baseModel):
    '''
    Admin interface for program
    '''
    
    def userAdd(self, fd ):
        '''
        Add a user to the system
        '''
        try:
            md5pass = hashlib.md5(fd['password'])
        except:
            return 'Failed'
        try:
            if fd.has_key('IsAdmin'):
                pass
            else:
                fd['IsAdmin'] = 0
            
            sql = """insert into users 
            ('uname', 'password', 'namefirst', 'namelast', 'email', 'isadmin' )
            values ('%s', '%s', '%s', '%s', '%s', '%s' );
            """ % (fd['name'], md5pass.hexdigest(), fd['namefirst'],fd['namelast'],fd['email'],fd['IsAdmin'])
            self.db.execute(sql)
        except:
            return 'Failed'
        
        return 'Success'
        
        
    def userDel(self, uid):
        '''
        Delete user from the system
        '''

        try:
            sql = "delete from events where mhostid = %s;" %(uid)
            self.db.execute(sql)
            sql = "delete from users where uid = %s;" %(uid)
            self.db.execute(sql)
            sql = "delete from attendees where uid = %s;" %(uid)
            self.db.execute(sql)
        except:
            return 'Failed'
        return 'Success'
        
        
    def userMod(self, fd):
        '''
        Modify user in system
        '''
        try:
            sql = """update users set 
            uname = '%s',
            namefirst = '%s',
            namelast = '%s',
            email = '%s',
            isadmin = '%s'
            where uid = %s
            """ % (fd['uname'], fd['namefirst'], fd['namelast'], fd['email'], fd['IsAdmin'], fd['userID'])
            #print "User Mod -> %s" %(sql)
            self.db.execute(sql)
        except:
            return "Failed"
        
        return 'Success'
    
    def roomAdd(self, fd):
        '''
        Add room to the system
        '''
        try:
            sql = """insert into rooms ('rname', 'rdescription', 'rsize' )
            values ('%s', '%s', '%s');""" %(fd['rname'], fd['rdescription'], fd['rsize'])
            self.db.execute(sql)
        except:
            return "Failed"
        
        return 'Success'
    def roomMod(self, fd):
        '''
        Modify room in system
        '''
        
        try:
            sql = """update rooms set 
            rname = '%s',
            rdescription = '%s',
            rsize = '%s'
            where rid = %s
            """ % (fd['rname'], fd['rdescription'], fd['rsize'], fd['roomID'])
            #print "Room Mod -> %s" %(sql)
            self.db.execute(sql)
        except:
            return "Failed"
        
        return 'Success'
    """    
    def roomDel(self, rid):
        '''
        Remove room in system
        Note: This action will remove all events containing 
        this room id. 
        '''
        try:
            sql = "delete from events where mroomid = %s;" %(rid)
            self.db.execute(sql)
            sql = "delete from rooms where rid = %s;" %(rid)
            self.db.execute(sql)
        except:
            return 'Failed'
        return 'Success'
    """


class roomControl(baseModel):
    '''
    Interface used to get room information
    '''
    def getRoomList(self):
        '''
        return full listing of rooms
        '''
        sql = "select * from rooms"
        result = self.db.query(sql)
        return result.fetchall()
    

    def getRoomSize(self, rid):
        '''
        return full listing of rooms
        '''
        sql = "select * from rooms where rid = %s" %(rid)
        result = self.db.query(sql)
        return result.fetchone()['rsize']

    
    def getWsRoomList(self):
        '''
        return full listing of rooms
        '''
        sql = "select * from rooms"
        result = self.db.query(sql)
        return self.getJsonResults(result)
    
    def getWsRoomById(self, rid):
        '''
        return full listing of rooms
        '''
        sql = "select * from rooms where rid = '%s'" %(rid)
        result = self.db.query(sql)
        data = self.getDataDict(result, 1)
        jsonData = self.getJsonData(data)
        print "Room Result = %s" %(jsonData)
        return jsonData


    def getRoomSizeGreaterThan(self, num):
        '''
        Return rooms large enough for number of guests
        '''
        sql = "select * from rooms where rsize >= %s" %(num)
        result = self.db.query(sql)
        return self.getDataDict(result)


'''
--- CLASS Calendar Control ---
'''
class calendarControl(baseModel):
    '''
    Handles Events
    Got this -> {'eventStrtTimeMer': u'2', 
    'eventStrtTimeHr': u'11', 
    'eventStrt': u'08/29/2011', 
    'eventDurationMin': u'0', 
    'selRoom': u'2', 
    'eventStrtTimeMin': u'30', 
    'selUser': [u'1', u'2', u'3'], 
    'eventDesc': u'Test Event', 
    'eventDurationHr': u'2'}
    '''
    
    
    def addEvent(self, eventData):
        '''
        Add an event to the calendar
              
        '''
        """
        dateParts = eventData['eventStrt'].split('/')
        eventHr = int(eventData['eventStrtTimeHr'])
        print "This is date Parts -> %s" %( dateParts)
        if (eventData['eventStrtTimeMer'] == 'p'):
            '''
            
            '''
            eventHr = eventHr + 12
            if (eventHr == 24) :
                eventHr = 0
        
        dateBaseConvert = datetime.datetime(int(dateParts[2]), int(dateParts[0]), int(dateParts[1]), eventHr, int(eventData['eventStrtTimeMin']))
        epochStart = time.mktime(dateBaseConvert.timetuple()).__int__()
        epochEnd = epochStart + ( int(eventData['eventDurationHr']) * 3600 ) +  int(eventData['eventDurationMin']) * 60
        """
        
        dateTs = self.getDateTs(eventData)
        
        sql = """insert into events
        (mdescription,
        mstarttime,
        mendtime,
        mhostid,
        mroomid,
        mstatusid
        )
        values ( '%s', %s, %s, %s, %s, %s );
        """ % (eventData['eventDesc'],
               dateTs[0], 
               dateTs[1], 
               eventData['HOST_ID'], 
               eventData['selRoom'], 
               1) 
        
        #print "SQL -> %s" %(sql)
        self.db.execute(sql)
        eventId = self.db.getLastRowId()
        
        if eventData.has_key('selUser'):
            for uid in eventData['selUser']:
                '''
                add users to the meeting
                Note: In this case asid (Attendee status ID [Are they attending])
                is set to the initial value of 0. This means "invited"
                '''
                sql = """insert into attendees 
                values (%s, %s, 0);
                """ % (eventId, uid)
                #print "Inserting SQL -> %s" %(sql)
                self.db.execute(sql)
        
        
        
        #            Test adding email to event creation
        try:
            mc = mailControl()
            mc.sendMail(eventData['selUser'], eventData['eventDesc'], eventId)
        
        except KeyError:
            '''
            If a list of users is not defined then 
            do not send out notifications
            '''
            print "[Calendar Control] No user invites defined. No email to send..."
        except:
            '''
            Something else failed.
            Log error
            '''
            print "[Calendar Control] Failed send email invite"
        
    
    def updateEvent(self, eventData):
        '''
        Add an event to the calendar
              
        '''
        
        
        dateTs = self.getDateTs(eventData)
        
        sql = """update events
        set mdescription = '%s',
        mstarttime = '%s',
        mendtime = '%s',
        mroomid = '%s'
        where mid = '%s';
        """ % (eventData['eventDesc'],
               dateTs[0], 
               dateTs[1], 
               eventData['selRoom'], eventData['formEventID']) 
        
        print "UPDATE EVENT SQL -> %s" %(sql)
        self.db.execute(sql)
        
        '''
        Remove all users
        '''
        sql = "delete from attendees where mid = %s" %(eventData['formEventID'])
        self.db.execute(sql)
        if eventData.has_key('selUser'):
            for uid in eventData['selUser']:
                '''
                add users to the meeting
                Note: In this case asid (Attendee status ID [Are they attending])
                is set to the initial value of 0. This means "invited"
                '''
                sql = """insert into attendees 
                values (%s, %s, 0);
                """ % (eventData['formEventID'], uid)
                #print "Inserting SQL -> %s" %(sql)
                self.db.execute(sql)
        
        
        
        #            Test adding email to event creation
        try:
            mc = mailControl()
            mc.sendMail(eventData['selUser'], eventData['eventDesc'], eventData['formEventID'], 'resched')
        
        except KeyError:
            '''
            If a list of users is not defined then 
            do not send out notifications
            '''
            print "[Calendar Control] No user invites defined. No email to send..."
        except:
            '''
            Something else failed.
            Log error
            '''
            print "[Calendar Control] Failed send email invite"
        
    def validateEvent(self):
        '''
        Determine if all resources are available
        '''
    def styleEvents(self, result, uid):
        '''
        Return a listing including event colors
        These colors will distinguish hosted vs attended events.  
        '''
        styleSet = self.getJsonResults(result)
        
        for row in styleSet:
            '''
            Inspect row and update events
            '''

            
    def getDateTs(self, dateData):
        '''
        Converts a start time and duration 
        to two epoch dates (start and end). 
        '''
        
        dateParts = dateData['eventStrt'].split('/')
        eventHr = int(dateData['eventStrtTimeHr'])
        print "This is date Parts -> %s" %( dateParts)
        if (dateData['eventStrtTimeMer'] == 'p'):
            '''
            
            '''
            eventHr = eventHr + 12
            if (eventHr == 24) :
                eventHr = 0
        
        dateBaseConvert = datetime.datetime(int(dateParts[2]), int(dateParts[0]), int(dateParts[1]), eventHr, int(dateData['eventStrtTimeMin']))
        epochStart = time.mktime(dateBaseConvert.timetuple()).__int__()
        epochEnd = epochStart + ( int(dateData['eventDurationHr']) * 3600 ) +  int(dateData['eventDurationMin']) * 60
        
        return (epochStart, epochEnd)
        
    
    def getWsDateTs(self, dateData):
        '''
        Return dates via web service
        '''
        return self.getJsonData(self.getDateTs(dateData))
        
        
     
    def getWsEventList(self, uid):
        '''
        Return a list of events based on the user id 
        that is requesting the schedule
        In general user can only see there own events...
        
        '''
        
        '''
        Get a list of events hosting and attending
        '''
        sql = """select 
        '/event/eventDetail?EventId=' ||e.mid as url, 
        e.mstarttime as start, 
        e.mendtime as end, 
        e.mdescription as title,
        0 as allDay
        from 
        events e 
        LEFT JOIN 
        attendees a 
        ON 
        e.mid = a.mid 
        where
        e.mstatusid = 1
        AND
        a.asid = 1
        AND
        a.uid = %s or e.mhostid = %s
        AND
        e.mstatusid = 1
        group by e.mid;
        """ % (uid, uid)
        #print "Getting Events -> %s" % (sql)
        result = self.db.query(sql)
        
        return self.getJsonResults(result)
    

    
    
    def checkForConflicts(self, formData, hostID):
        
        print """ CHECK CONFLICT
        
        
        
        """
        conflictList = {}
        attendeeList = {}
        hostingList = {}
        conflict = {}
        
        '''
        Given the proposed new meeting time-frame,
        review scheduled items looking for conflicts
        Return results to calling method.
        
        Look for the following conflicts
        1.) Room: Availability 
        2.) Attendee: Availability
        3.) Host: Availability
        4.) RoomSize: Size >= Number of attendees plus host
        
        '''
        eventTs = self.getDateTs(formData)
        # Check for host and attendee conflicts
        sql = """ select
        e.mid as event, 
        e.mstarttime as start, 
        e.mendtime as end, 
        e.mdescription as title,
        e.mhostid as host,
        e.mroomid as room,
        a.uid as attendee,
        a.asid as attendRespId
        from 
        events e 
        LEFT JOIN 
        attendees a 
        ON 
        e.mid = a.mid 
        where 
        e.mstarttime BETWEEN %(start)s and %(end)s
        OR
        e.mendtime BETWEEN %(start)s and %(end)s
        AND
        e.mstatusid = 1;
        """ % {'start': eventTs[0], 'end': eventTs[1]}
        

        print "Conflict Check SQL - > %s" %(sql)
        result = self.db.query(sql)
        
        if formData.has_key('selUser'):
            '''
            If an initial group is defined add the length
            '''
            groupSize = formData['selUser'].__len__() + 1
        else:
            '''
            If the group is not defined
            account for the requester
            '''
            groupSize = 1
        '''
        Call the room control and get the size of the REQUESTED
        room ID
        '''
        thisRc = roomControl()
        roomSize = thisRc.getRoomSize(formData['selRoom'])
        
        print "Room Size = %s, Group Size: %s" %(roomSize, groupSize)
        
        if groupSize > roomSize:
            '''
            Add an element to the conflict list
            '''
            conflictList['RoomSize'] = 1
            conflictList['RoomSize'] = {'GroupSize': groupSize, 'RoomSize': roomSize}
            
        
        for event in result:
            
            print "TESTING EVENTS\n\n"
            '''
            Inspect each event looking for conflicts
            '''
            
            # Host conflict
            if (hostID == event['host']):
                '''
                if the host is listed in the discovered list 
                then set the host exception 
                Set the host Conflict list
                '''
                print "type : %s " %(formData['formEventID'])
                #if formData.has_key('formEventID'):
                if formData['formEventID']:
                    '''
                    Compare db hosted event to requested event
                    if they are the same skip the host check 
                    
                    '''
                    if int(formData['formEventID']) == int(event['mid']):
                        '''
                        Skip check
                        '''
                        print "Conditional check "
                        pass
                    else:
                        '''
                        do check
                        '''
                        print "CHECK CONFLICT HOST "
                        conflictList['Host'] = 1
                else:
                    print "CHECK CONFLICT HOST "
                    conflictList['Host'] = 1
                    
                
            
            # Attendee conflict
            print "selUserField: %s- Type: %s" %(formData['selUser'], type(formData['selUser']))
            try:
                if (type(formData['selUser']).__name__ == 'unicode'):
                    '''
                    Process single value
                    See if the attendee has accepted a meeting during this time
                    '''
                    if int(formData['selUser']) == int(event['attendee']):
                        '''
                        Set the attendee in the listing
                        '''
                        if event['attendRespId'] == 1:
                            '''
                            Attendee is not free
                            Register a conflict
                            '''
                            print " Match Found..."
                            attendeeList[int(formData['selUser'])] = 1
                            conflictList['Attendee'] = attendeeList
                    
                    '''
                    See if the user is hosting a meeting during this time
                    '''
                    print "TESTING HOSTING CONFLICT user: %s Host: %s" %(formData['selUser'], event['host'])
                    if int(formData['selUser']) == int(event['host']):
                        
                        
                        hostingList[formData['selUser']] = 1
                        conflictList['isHosting'] = hostingList
                        
                
                elif(type(formData['selUser']).__name__ == 'list'):
                    '''
                    Process list of vales from form...
                    '''
                
                    for attendee in formData['selUser']:
                        '''
                        Check if the attendees listing in the proposed
                        invite list are available. If they are listed 
                        set the exception listing
                        '''
                        print "Requested %s and DB %s" % (attendee, event['attendee'] )
                        if int(attendee) == int(event['attendee']):
                            '''
                            Set the attendee in the listing
                            '''
                            if event['attendRespId'] == 1:
                                '''
                                Attendee is not free
                                Register a conflict
                                '''
                                
                                print " Match Found..."
                                attendeeList[int(attendee)] = 1
                                conflictList['Attendee'] = attendeeList
                        
                        '''
                        See if the user is hosting a meeting during this time
                        '''
                        if int(attendee) == int(event['host']):
                            hostingList[int(attendee)] = 1
                            conflictList['isHosting'] = hostingList
            
            except:
                '''
                If this is a room reservation request then
                do not check for attendees, simply move on with 
                checks
                '''
                print "[Conflict Check ]List of users not defined"
                pass
                
            # Room Conflict
            if int(formData['selRoom']) == event['room']:
                '''
                If the room is not available then set the list...
                '''
                print "[Room Conflict Test] The room is conflicted...."
                conflictList['Room'] = 1
                
        
        '''
        Check for conflicts
        '''

            
        
        print "[CONFLICT LIST ]conflict List -> %s" %(conflictList)

        '''
        Get a listing of all events during this time period
        Got this from the form -> {
        'eventStrtTimeMer': u'a', 
        'eventStrtTimeHr': u'8', 
        'eventStrt': u'08/31/2011', 
        'eventDurationMin': u'0', 
        'selRoom': u'4', 
        'eventStrtTimeMin': u'00', 
        'selUser': [u'1', u'2', u'3', u'4'], 
        'eventDesc': u'Test Call Back', 
        'eventDurationHr': u'1'
        }
        '''
        jsonData = self.getJsonData(conflictList)
        
        return jsonData

    
    
    
    def getWsCurrentEvents(self, uid, eventNum=10):
        '''
        Get a listing of events from the database
        '''
        now = self.getNowTs()
        
        
        sql = """select 
        '/event/eventDetail?EventId=' ||e.mid as url, 
        e.mstarttime as start, 
        e.mendtime as end, 
        e.mdescription as title,
        r.rname as room
        from 
        events e 
        LEFT JOIN 
        attendees a 
        ON 
        e.mid = a.mid 
        LEFT JOIN
        rooms r
        ON
        r.rid = e.mroomid
        where 
        a.uid = %(uid)s or e.mhostid = %(uid)s
        AND
        e.mstarttime > %(nowts)s
        group by e.mid
        order by e.mstarttime asc;""" %{'uid':uid, 'nowts':now}
        
        #print "SQL -> %s" %(sql)
        
        return self.getJsonResults(self.db.query(sql))

    def doCancelEvent(self, data):
        '''
        Process the event to cancel an appointment
        '''
        sql = """update events set mstatusid = 2 where mid = '%s';""" %(data['EventID'])
        #print "Cancel SQL -> %s " %(sql)
        self.db.execute(sql)
    def doAcceptEvent(self, data):
        '''
        Process the event to cancel an appointment
        '''
        sql = """update attendees set asid = 1 where mid = '%s' AND uid = '%s';""" %(data['EventID'], data['UID'])
        #print "Accept SQL -> %s " %(sql)
        self.db.execute(sql)
    
    def doDeclineEvent(self, data):
        '''
        Process the event to cancel an appointment
        '''
        sql = """update attendees set asid = 2 where mid = '%s' AND uid = '%s';""" %(data['EventID'], data['UID'])
        #print "Decline SQL -> %s " %(sql)
        self.db.execute(sql)
    
    
    def getWsEventById(self, mid):
        '''
        Package event data
        include event details and attendee list...
        '''
        print "Calling  get EVENT ------"
        sql = """select * from events where mid = '%s' """ % (mid)
        event = self.getDataDict(self.db.query(sql), 1)
        sql = """select * from attendees where mid = '%s' """ % (mid)
        attendees  = self.getDataDict(self.db.query(sql))
        event['START'] = self.getDateFromTs(event['mstarttime'])
        event['ATTEND_LIST'] = attendees
        
        startIndex = self.getDatePartsFromTs(event['mstarttime'])
        if (startIndex[3] > 12):
            event['START_HR'] = startIndex[3] - 12
            event['MER'] = 'p'
        else:
            event['START_HR'] = startIndex[3]
            event['MER'] = 'a'
        
        
        event['START_MIN'] = startIndex[4]
        
        durSet = self.getDurFromTs(event['mstarttime'], event['mendtime'])
        
        event['DUR_HR'] = durSet['HOUR']
        event['DUR_MIN'] = durSet['MIN']
        
        
        eventData = self.getJsonData(event)
        print "Event Data => %s" %(eventData)
        return eventData
    
    
        
    
    
class mailControl(baseModel):
    
    def  __init__(self):
        '''
        Model control for mail services
        '''
        self.email = mailService()
        self.uc = userControl()
        try:
            self.email.loadConfig()
        except:
            print "Error Failed to load email config!!"

    def __setEmailList(self, list):
        '''
        Interfaces to sent to email
        '''
        if (type(list).__name__ == 'unicode'):
            '''
            Not a list, process it as such
            '''
            #print "Emailing single user"
            user = self.uc.getUserById(list)
            self.email.setAddr(user['email'])
            
            
        else:
            '''
            Given a list of user ids
            collect user information and populate the
            list of email addresses
            Is a list, and iterable
            '''
            for userid in  list:
                user = self.uc.getUserById(userid)
                self.email.setAddr(user['email'])
        
    
    def sendMail(self, uids, eventName ,mid, type='invite'):
        '''
        send email to all attendees
        '''
        
        self.__setEmailList(uids)
        self.email.sendMsg(eventName, mid, type)
        
        
        
        
        
        
        
        


